import datetime
import time
from extensions import Model, db
from sqlalchemy import Column, DateTime, Index, Integer, String, Text, text, Boolean, TIMESTAMP, BigInteger
from .utils.decorators import with_session
from flask_restful import current_app


class StockGood(Model):
    __tablename__ = 'stock_goods'

    id = Column(Integer, primary_key=True)
    goods_name = Column(String(128), nullable=False)
    sku_id = Column(String(20), nullable=False, unique=True)
    category_id = Column(Integer, nullable=False)
    is_set_scale = Column(Integer, nullable=False)
    is_rx = Column(Integer, nullable=False)
    is_otc = Column(Integer, nullable=False)
    common_name = Column(String(128), nullable=False)
    specification = Column(String(128), nullable=False)
    manufacturer = Column(String(128), nullable=False)
    sync_at = Column(DateTime, nullable=False)
    created_at = Column(DateTime, nullable=False)
    updated_at = Column(DateTime, nullable=False)

class Stockdetail(Model):
    __tablename__ = 'stock_stockdetail'

    id = Column(Integer, primary_key=True)
    operation_time = Column(DateTime, nullable=False)
    warehouse_id = Column(String(128), nullable=False)
    warehouse_name = Column(String(128), nullable=False)
    business_type = Column(Integer, nullable=False)
    business_no = Column(String(255), nullable=False, index=True)
    wms_seq = Column(String(32), nullable=False, index=True, doc="wms业务流水好")
    order_id = Column(String(128), nullable=False)
    status_id = Column(Integer, nullable=False)
    sku_id = Column(String(20), nullable=False, index=True)
    amount = Column(Integer, nullable=False)
    original_real_stock = Column(Integer, nullable=False)
    real_stock_balance = Column(Integer, nullable=False)
    original_occupy_stock = Column(Integer, nullable=False)
    occupy_stock_balance = Column(Integer, nullable=False)
    user_id = Column(String(128), nullable=False)
    remark = Column(String(255))
    created_at = Column(DateTime, nullable=False)
    updated_at = Column(DateTime, nullable=False)
    order_type = Column(Integer, nullable=False)
    ofc_order = Column(String(50), server_default=text("''"))
    sys_id = Column(String(128), server_default=text("''"))

    @staticmethod
    @with_session
    def new_record(log_data):
        sql = """
        insert into
         stock.stock_stockdetail
         (operation_time,
          warehouse_id,
          warehouse_name,
          business_type,
          business_no,
          order_id,
          sku_id,
          amount,
          original_real_stock,
          original_occupy_stock,
          real_stock_balance,
          occupy_stock_balance,
          user_id,
          remark,
          created_at,
          updated_at,
          order_type) select
                      '{operation_time}',
                      '{warehouse_id}',
                      '{warehouse_name}',
                      {business_type},
                      '{business_no}',
                      '{order_id}',
                      '{sku_id}',
                      {amount},
                      {original_real_stock},
                      {original_occupy_stock},
                      {real_stock_balance},
                      {occupy_stock_balance},
                      '{user_id}',
                      '{remark}',
                      '{created_at}',
                      '{updated_at}',
                      {order_type}
          from
           DUAL
          where
           not exists (
            select
             1
            from
             stock.stock_stockdetail
            where
             order_id='{order_id}'
            and sku_id='{sku_id}'
            and remark='1'
           )
          """
        current_app.logger.info(sql)
        sql = sql.format(**log_data)
        db.session.execute(sql)

class Realstock(Model):

    STATUS_ID_DEFAULT = 1

    __tablename__ = 'stock_realstock'
    __table_args__ = (
        Index('stock_realstock_warehouse_id_45c5e72fad2ce32e_uniq',
              'warehouse_id', 'sku_id', 'status_id', unique=False),
    )

    id = Column(Integer, primary_key=True)
    warehouse_id = Column(String(128), nullable=False)
    sku_id = Column(String(20), nullable=False)
    status_id = Column(Integer, nullable=False)
    real_stock = Column(Integer, nullable=False, default=0)
    occupy_stock = Column(Integer, nullable=False, default=0)
    separate_stock = Column(Integer, nullable=False, default=0)
    lock_stock = Column(Integer, nullable=False, default=0)
    remark = Column(String(255), nullable=True)
    created_at = Column(DateTime, nullable=False,
                        default=datetime.datetime.now)
    updated_at = Column(DateTime, nullable=False,
                        default=datetime.datetime.now)
    version = Column(BigInteger, nullable=False, default=0)
    lock_at = Column(DateTime, nullable=True, default=datetime.datetime.now)

    @staticmethod
    @with_session
    def new_record(warehouse_id, sku_id, status_id, real_stock, occupy_stock, version=1):
        insert_sql = """
        -- insert_sql
        INSERT INTO stock_realstock (
            warehouse_id,
            sku_id,
            status_id,
            real_stock,
            occupy_stock,
            created_at,
            updated_at,
            separate_stock,
            version
        ) SELECT
            '{warehouse_id}',
            '{sku_id}',
            {status_id},
            {real_stock},
            {occupy_stock},
            '{date_str}',
            '{date_str}',
            0,
            0
        FROM
            DUAL
        WHERE
            NOT EXISTS (
                SELECT
                    1
                FROM
                    stock_realstock
                WHERE
                    stock_realstock.warehouse_id='{warehouse_id}'
                AND stock_realstock.sku_id='{sku_id}'
                AND stock_realstock.status_id={status_id}
            )
        """.format(warehouse_id=warehouse_id,
                   sku_id=sku_id,
                   status_id=status_id,
                   real_stock=real_stock,
                   occupy_stock=occupy_stock,
                   date_str=time.strftime("%Y-%m-%d %H:%M:%S"))
        current_app.logger.info("new realstock sql: %s" % insert_sql)
        db.session.execute(insert_sql)
        db.session.flush()


class StockWarehouse(Model):
    __tablename__ = 'stock_warehouse'

    DELIVERY_SYSTEM_WMS = 'WMS'
    DELIVERY_SYSTEM_POP = 'POP'

    id = Column(Integer, primary_key=True, autoincrement=True)
    warehouse_id = Column(String(32), nullable=False)
    deliver_system = Column(String(20), nullable=True)
    deliver_method = Column(String(20), nullable=True)
    support_logistics = Column(Boolean, default=False, nullable=False, doc='是否支持物流配送')
    support_medicare = Column(Boolean, default=False, nullable=False, doc='是否支持医保自提')
    warehouse_name = Column(String(128), nullable=False)
    allow_negative = Column(Integer, nullable=False, default=0)
    remark = Column(String(255), nullable=True)
    is_lock = Column(Boolean, default=False, nullable=False, doc='是否锁定（冻结）改仓库')
    is_sys = Column(Boolean, default=False, nullable=False, doc='是否系统仓库')
    displayorder = Column(Integer, default=0, nullable=False, doc='仓库排序（权重）')
    created_at = Column(DateTime, nullable=False,
                        default=datetime.datetime.now)
    updated_at = Column(DateTime, nullable=False,
                        default=datetime.datetime.now)
    lock_at = Column(DateTime, nullable=True, doc='锁定的时间，这个时间可以用来限制当前的仓库被锁定的时间',
                     default=datetime.datetime.now)

    def lock_release_at(self, expired=None):
        """
            判断仓库锁定的时间，如果是超过expired了，则解锁，否则依旧锁定
            :param expired:
        """
        pass

    @staticmethod
    @with_session
    def update_warehouse(warehouse_id, warehouse_name, allow_negative=0, remark=''):
        record = StockWarehouse.query.filter_by(
            warehouse_id=warehouse_id).first()
        if not record:
            record = StockWarehouse(
                warehous_id=warehouse_id,
                warehouse_name=warehouse_name,
                allow_negative=allow_negative,
                remark=remark
            )
            db.session.add(record)

    def __repr__(self):
        return '<仓库编码: %r, 仓库名称: %r>' % (self.warehouse_id, self.warehouse_name)


class Setting(Model):

    __tablename__ = 'stock_setting'

    id = Column(Integer, primary_key=True, autoincrement=True)
    cfg_key = Column(String(60), nullable=False, doc='配置KEY', unique=True)
    cfg_value = Column(Text, doc='配置KEY对应的内容')
    cfg_type = Column(String(32), nullable=False,
                      doc='配置Value的值类型：string, json')
    status = Column(Boolean, default=True, doc='是否启用')
    created_at = Column(TIMESTAMP, nullable=False)